﻿using Shop.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Shop.Implementation.SO
{
    public class SoPromote
    {
        public static string GetCusPromote(SqlCommand cncmd, int iMaxID, string _tbMain, string _tbDetail, int idcus)
        {
            string cmdsql = "if(object_id('tempdb..#tmpYH') is not null) drop table #tmpYH";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tmpYH(AutoID int,ID int,cPromoteName nvarchar(200),iValue float)";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            DataTable dtPromoteRule = new DataTable();

            cmdsql = string.Format("select ID,cRule from CustomerPromote where dDateStart<'{0}' and dDateEnd>='{0}'", DateTime.Now.ToString("yyyy-MM-dd"));
            cncmd.CommandText = cmdsql;
            dtPromoteRule.Load(cncmd.ExecuteReader());

            for (int i = 0; i < dtPromoteRule.Rows.Count; i++)
            {
                string[] s = dtPromoteRule.Rows[i]["cRule"].ToString().Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
                Dictionary<string, object> ofilter = new Dictionary<string, object>();
                for (int k = 0; k < s.Length; k++)
                {
                    switch (s[k])
                    {
                        case "@总金额":
                            ofilter.Add(s[k], GetSoSum(cncmd, iMaxID, _tbMain, _tbDetail, idcus));
                            break;
                        case "@购买次数":
                            ofilter.Add(s[k], GetGMCS(cncmd, iMaxID, _tbMain, _tbDetail, idcus));
                            break;
                        case "@当天购买次数":
                            ofilter.Add(s[k], GetGMCS_Today(cncmd, iMaxID, _tbMain, _tbDetail, idcus));
                            break;
                        default:
                            System.Reflection.MethodBase.GetCurrentMethod().WriteErrLog("无此关键字");
                            break;
                    }
                }
                DataTable dtProDetail = new DataTable();
                cmdsql = "select AutoID,ID,cPromoteName,cValue,cRule from CustomerPromoteDetail where id=" + dtPromoteRule.Rows[i]["ID"].ToString();
                cncmd.CommandText = cmdsql;
                dtProDetail.Load(cncmd.ExecuteReader());
                cmdsql = "insert into  #tmpYH(AutoID,ID,cPromoteName,iValue) ";
                for (int k = 0; k < dtProDetail.Rows.Count; k++)
                {
                    cmdsql += string.Format(@"select AutoID,ID,cPromoteName,{0} from CustomerPromoteDetail where {1} and cRule='{1}'
                        union ",
                        dtProDetail.Rows[k]["cValue"], dtProDetail.Rows[k]["cRule"]);
                }
                if (dtProDetail.Rows.Count > 0)
                {
                    cmdsql = cmdsql.Substring(0, cmdsql.Length - "union ".Length);
                }
                cncmd.CommandText = cmdsql;
                foreach (string keyname in ofilter.Keys)
                {
                    cncmd.Parameters.Add(new SqlParameter(keyname, ofilter[keyname]));
                }
                cncmd.ExecuteNonQuery();
                cncmd.Parameters.Clear();
            }
            cmdsql = string.Format(@"delete from #tmpYH where autoid in (select isnull(IDPromote,0) from sodetailpre inner join sopre on sodetailpre.id=sopre.id
                    where sopre.istatus<>-1 and DATEDIFF(day, sopre.dDate,getdate())=0 and sopre.idcus={0})", idcus);
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "delete from #tmpYH where autoid in (select autoid from CustomerPromoteDetail where iQuantityTotal<iQuantity)";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            return "#tmpYH";
        }
        #region 公式
        /// <summary>
        /// 获取客户的@购买次数
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="iMaxID"></param>
        /// <param name="_tbMain"></param>
        /// <param name="_tbDetail"></param>
        /// <returns></returns>
        public static int GetGMCS(SqlCommand cncmd, int iMaxID, string _tbMain, string _tbDetail ,int idcus)
        {
            string cmdsql = string.Format("select count(*) from SoPre where IDCus={0} ", idcus, iMaxID);
            DataTable dtjud = new DataTable();
            cncmd.CommandText = cmdsql;
            dtjud.Load(cncmd.ExecuteReader());
            int iCountXD = 0;
            if (dtjud.Rows.Count > 0)
            {
                iCountXD = (int)dtjud.Rows[0][0];
            }
            if (_tbMain.Substring(0, 1) == "#")
            { iCountXD += 1; }
            return iCountXD;
        }

        /// <summary>
        /// 获取客户的@当天购买次数
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="iMaxID"></param>
        /// <param name="_tbMain"></param>
        /// <param name="_tbDetail"></param>
        /// <returns></returns>
        public static int GetGMCS_Today(SqlCommand cncmd, int iMaxID, string _tbMain, string _tbDetail,int idcus )
        {
            string cmdsql =string.Format("select count(*) from So where convert(nvarchar,dDate,23)=convert(nvarchar,GETDATE(),23) and IDCus={0}  ", idcus, iMaxID);
            DataTable dtjud = new DataTable();
            cncmd.CommandText = cmdsql;
            dtjud.Load(cncmd.ExecuteReader());
            int iCountXD = 0;
            if (dtjud.Rows.Count > 0)
            {
                iCountXD = (int)dtjud.Rows[0][0];
            }
            if (_tbMain.Substring(0, 1) == "#")
            { iCountXD += 1; }
            return iCountXD;
        }

        /// <summary>
        /// 获取订单总金额
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="iMaxID"></param>
        /// <param name="_tbMain"></param>
        /// <param name="_tbDetail"></param>
        /// <returns></returns>
        public static double GetSoSum(SqlCommand cncmd, int iMaxID, string _tbMain, string _tbDetail, int idcus)
        {
            string cmdsql = string.Format("select SUM(iSum) from {0}  where id={1} ", _tbDetail, iMaxID);
            cncmd.CommandText = cmdsql;
            double iSoSum = (cncmd.ExecuteScalar()).GetVal_Double();
            return iSoSum;
        }
        /// <summary>
        /// 获取客户当天购买的总金额
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="iMaxID"></param>
        /// <param name="_tbMain"></param>
        /// <param name="_tbDetail"></param>
        /// <param name="idcus"></param>
        /// <returns></returns>
        public static double GetCusSum(SqlCommand cncmd, int iMaxID, string _tbMain, string _tbDetail, int idcus)
        {
            string cmdsql = string.Format("select IDCus from {0} where id={1} ", _tbMain, iMaxID);
            cncmd.CommandText = cmdsql;
            DataTable dtjud = new DataTable();
            dtjud.Load(cncmd.ExecuteReader());
            int IDCus = (dtjud.Rows[0]["IDCus"]).GetVal_Int();

            cmdsql = string.Format("select sum(sodetailpre.iSum) from SoPre inner join sodetailpre on sopre.id=sodetailpre.id where dDate=convert(nvarchar,GETDATE(),23) and istatus=0 and IDCus={0}  ", IDCus);
            cncmd.CommandText = cmdsql;
            double iSum = (cncmd.ExecuteScalar()).GetVal_Double();

            if (_tbMain.Substring(0, 1) == "#")
            {
                cmdsql = string.Format("select sum(iSum) from {0}  ", _tbDetail);
                cncmd.CommandText = cmdsql;
                iSum += (cncmd.ExecuteScalar()).GetVal_Double();
            }
            return iSum;
        }
        #endregion
    }
}